/+
Example sample_01.d - Example of how to use the dbapi library.

Copyright (C) 2007 Myron Alexander (myron.alexander...gmail.com)

Licence: Public domain
+/

import std.boxer;
import std.stdio;
import std.string;

import dbapi   = dbapi.base;
import sqlite3 = dbapi.sqlite3;

import dbapi.helper;

alias dbapi.NamedParameter   np;
alias dbapi.unboxNullable    unboxN;
alias dbapi.unboxNullDefault unboxD;
alias dbapi.isNull           isNull;
alias dbapi.BatchBuilder     BatchBuilder;
alias dbapi.tNullFlag        tNullFlag;

void main () {

   auto c = sqlite3.connect ("MyDb.db", sqlite3.TransactionMode.ON_MODIFY);
   //auto c = sqlite3.connect ("MyDb.db", sqlite3.TransactionMode.AUTO_COMMIT);
   //auto c = sqlite3.connect ("MyDb.db", sqlite3.TransactionMode.ALWAYS);
   //auto c = sqlite3.connect ("MyDb.db", sqlite3.TransactionMode.USER);
   scope (exit) c.close ();

   auto cu = c.cursor ();
   scope (exit) cu.close ();

   //---------------------------------------------------------------------------

   writefln ("\n=== Table Creation =========================================");

   /* SQL statements must not end with a semi-colon; a multi-statement error
    * will be generated if the semi-colon is used to terminate the statement.
    */
   cu.execute (
      "create table atable (id int, name varchar2 (20), "
      "balance number (10,2))");

   writefln ("\n=== Populate Table =========================================");

   cu.execute ("insert into atable values (1,'First Person',0)");

   cu.execute ("insert into atable values (?,?,?)", 2, "Second Person", 1);

   cu.execute ("insert into atable values (?,?,?)", 3, "Third Person", 123.45);

   cu.execute (
      "insert into atable values (:id, :name, :balance)",
      np("id",4), np("name","Fourth Person"), np("balance",999.99));

   //---------------------------------------------------------------------------

   cu.execute ("select * from atable");

   writefln ("\n=== Table Description ======================================");

   outputDescription (cu);

   writefln ("\n=== select * from atable (no nulls) ========================");

   /* If no nulls are possible, then you can use unbox directly.
    */
   Box[][] values = cu.fetchall ();
   printValues (values);

   //---------------------------------------------------------------------------

   writefln ("\n=== select * from atable where name = :name ================");

   cu.execute (
      "select * from atable where name = :name",
      np("name", "Third Person"));
   values = cu.fetchall ();
   printValues (values);

   //---------------------------------------------------------------------------

   cu.execute ("insert into atable values (?,?,?)", null, null, null);

   cu.execute ("select * from atable");

   writefln ("\n=== select * from atable (nulls) ===========================");

   /* If nulls are expected, then you can use unboxNullable or unboxNullDefault.
    */
   values = cu.fetchall ();
   foreach (Box[] val; values) {
      writefln (
         "%d, %s, %#.2f",
         unboxD!(int)(val[0], -1), unboxD!(char[])(val[1], "<NULL>"),
         unboxD!(double)(val[2], -1.0));
   }

   //---------------------------------------------------------------------------

   cu.execute ("select * from atable");

   writefln ("\n=== select * from atable (alt-nulls) =======================");

   /* Alternatively, you can use the helper functions.
    */
   values = cu.fetchall ();
   foreach (Box[] val; values) {
      writefln (
         "%d, %s, %#.2f",
         val.xint (0), val.xstr (1, "<NULL>"), val.xdbl (2, 0.00));
   }
   
   writefln ("------------------------------------------------------------");
   
   foreach (Box[] val; values) {
      writefln (
         "%d, %s, %#.2f",
         xint (val[0]), xstr (val[1], "<NULL>"), xdbl (val[2], 0.00));
   }

   //---------------------------------------------------------------------------

   writefln ("\n=== Insert multiple ========================================");

   int[] affectedValues =  cu.executemany (
                              "insert into atable values (?,?,?)"
                              , boxArray (10, "Multi 1", 1.0)
                              , boxArray (11, "Multi 2", 2.0)
                              , boxArray (12, "Multi 3", 3.0)
                              , boxArray (13, "Multi 4", 4.0)
                              //, boxArray (14, "Multi 5", 5.0)
                           );

   writefln ("------------------------------------------------------------");

   writef ("Affected rows: [");
   foreach (i; affectedValues) {
      writef  (" %d ", i);
   }
   writefln ("]");

   writefln ("------------------------------------------------------------");

   cu.execute ("select * from atable");

   while ((values = cu.fetchmany (2)) !is null) {
      foreach (Box[] val; values) {
         writefln (
            "%d, %s, %#.2f",
            val.xint (0), val.xstr (1, "<NULL>"), val.xdbl (2, 0.00));
      }
      writefln ("   ++++++++++++++++++++");
   }
   
   //---------------------------------------------------------------------------

   writefln ("\n=== Insert multiple with BatchBuilder ======================");

   BatchBuilder batch = new BatchBuilder (2, 3);
   
   batch
      .append (20, "Batch 1", 1.0)
      .append (21, "Batch 2", 2.0)
      .append (22, "Batch 3", 3.0)
      .append (23, "Batch 4", 4.0)
      .append (24, "Batch 5", 5.0);
   
   affectedValues =
      cu.executemany ("insert into atable values (?,?,?)", batch.toArray());

   writefln ("------------------------------------------------------------");

   writef ("Affected rows: [");
   foreach (i; affectedValues) {
      writef  (" %d ", i);
   }
   writefln ("]");

   writefln ("\n=== Retrieve using type aware fetch method =================");

   cu.execute ("select * from atable");

   while (cu.next ()) {
      auto row = cu.fetchrow!(int, char[], double) ();
      writefln ("%d, %s, %#.2f"
         , row.nullFlag[0] ? 0        : row.c[0]
         , row.nullFlag[1] ? "<NULL>" : row.c[1]
         , row.nullFlag[2] ? 0.00     : row.c[2] );
   }

   writefln ("\n=== Retrieve using many rows method ========================");

   cu.execute ("select * from atable");
   
   {
      dbapi.Row!(int, char[], double)[] rows;
      while ((rows = cu.fetchmanyrows!(typeof(rows[0].c)) (3)) !is null) {
         foreach (r; rows) {
            writefln ("%d, %s, %#.2f"
               , r.nullFlag[0] ? 0        : r.c[0]
               , r.nullFlag[1] ? "<NULL>" : r.c[1]
               , r.nullFlag[2] ? 0.00     : r.c[2] );
         }
         writefln ("   ++++++++++++++++++++");
      }
   }

   writefln ("\n=== Retrieve using all rows method =========================");

   cu.execute ("select * from atable");
   
   {
      auto rows = cu.fetchallrows!(int, char[], double) ();
      
      foreach (r; rows) {
         writefln ("%d, %s, %#.2f"
            , r.nullFlag[0] ? 0        : r.c[0]
            , r.nullFlag[1] ? "<NULL>" : r.c[1]
            , r.nullFlag[2] ? 0.00     : r.c[2] );
      }

      rows = cu.fetchallrows!(int, char[], double) ();

      if (rows !is null) writefln ("\nERROR: fetchallrows left a row");
      if (cu.rowAvailable) writefln ("\nERROR: fetchallrows left a row");
   }

   writefln ("\n=== Retrieve using struct method ===========================");

   cu.execute ("select * from atable");

   struct Atable {
      int    id;
      char[] name;
      double balance;

      tNullFlag[3] nullFlag;
   }

   while (cu.next ()) {
      Atable atable = cu.fetchstruct!(Atable) ();
      writefln ("%d, %s, %#.2f"
         , atable.nullFlag[0] ? 0        : atable.id
         , atable.nullFlag[1] ? "<NULL>" : atable.name
         , atable.nullFlag[2] ? 0.00     : atable.balance );
   }

   writefln ("\n=== Retrieve using many structs method =====================");

   cu.execute ("select * from atable");

   {
      Atable[] atables;
      while ((atables = cu.fetchmanystructs!(Atable) (4)) !is null) {
         foreach (atable; atables) {
            writefln ("%d, %s, %#.2f"
               , atable.nullFlag[0] ? 0        : atable.id
               , atable.nullFlag[1] ? "<NULL>" : atable.name
               , atable.nullFlag[2] ? 0.00     : atable.balance );
         }
         writefln ("   ++++++++++++++++++++");
      }
   }

   writefln ("\n=== Retrieve using all structs method ======================");

   cu.execute ("select * from atable");
   
   {
      auto atables = cu.fetchallstructs!(Atable) ();
      foreach (atable; atables) {
         writefln ("%d, %s, %#.2f"
            , atable.nullFlag[0] ? 0        : atable.id
            , atable.nullFlag[1] ? "<NULL>" : atable.name
            , atable.nullFlag[2] ? 0.00     : atable.balance );
      }
   }

   //---------------------------------------------------------------------------

   writefln ("\n=== Commit =================================================");
   c.commit ();

   writefln ("\n=== Rollback ===============================================");
   c.rollback ();
}


void outputDescription (sqlite3.Cursor cu) {
   dbapi.ColumnAttributes[] caa = cu.description;
   foreach (dbapi.ColumnAttributes a ; caa) {
      writefln ("[%s, %s, %d, %d]", a.name, a.typeCode, a.precision, a.scale);
   }
}

void printValues (Box[][] values) {
   if (values is null) {
      writefln ("Nothing returned");
   } else {
      foreach (Box[] val; values) {
         writefln (
            "%d, %s, %#.2f",
            unbox!(int)(val[0]), unbox!(char[])(val[1]),
            unbox!(double)(val[2]));
      }
   }
}
